package com.xiaoxin.nmzp.server.mapper;

import com.xiaoxin.nmzp.server.entity.domain.NmzpUserTarget;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xiaoxin.nmzp.server.entity.vo.MsgItemVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
* @author xiaoxin
* @description 针对表【nmzp_user_target】的数据库操作Mapper
* @createDate 2024-11-07 08:23:18
* @Entity com.xiaoxin.nmzp.server.entity.domain.NmzpUserTarget
*/
@Mapper
public interface NmzpUserTargetMapper extends BaseMapper<NmzpUserTarget> {

    @Select("select count(*) from nmzp_user_target where (user_id = #{userId} and target_id = #{targetId}) or (user_id = #{targetId} and target_id = #{userId})")
    public Integer selectUserTarget(@Param("userId") Long userId, @Param("targetId") Long targetId);

    @Select("WITH UserMessages AS (\n" +
            "\tSELECT\n" +
            "\t\tm.sender_id,\n" +
            "\t\tm.recipient_id,\n" +
            "\t\tm.message_id,\n" +
            "\t\tm.msg,\n" +
            "\t\tm.create_time,\n" +
            "\t\tROW_NUMBER() OVER ( PARTITION BY CASE WHEN m.sender_id = 4 THEN m.recipient_id ELSE m.sender_id END ORDER BY m.create_time DESC ) AS rn \n" +
            "\tFROM\n" +
            "\t\tnmzp_message m \n" +
            "\tWHERE\n" +
            "\t\tm.sender_id = #{userId} \n" +
            "\t\tOR m.recipient_id = #{userId} \n" +
            "\t),\n" +
            "\tLatestMessages AS (\n" +
            "\tSELECT\n" +
            "\t\tum.sender_id,\n" +
            "\t\tum.recipient_id,\n" +
            "\t\tum.message_id,\n" +
            "\t\tum.msg,\n" +
            "\t\tum.create_time \n" +
            "\tFROM\n" +
            "\t\tUserMessages um \n" +
            "\tWHERE\n" +
            "\t\tum.rn = 1 \n" +
            "\t) SELECT\n" +
            "\tlm.sender_id,\n" +
            "\tu1.nick_name AS sender_name,\n" +
            "\tlm.recipient_id,\n" +
            "\tu2.nick_name AS recipient_name,\n" +
            "\tlm.message_id,\n" +
            "\tlm.msg,\n" +
            "\tlm.create_time \n" +
            "FROM\n" +
            "\tLatestMessages lm\n" +
            "\tJOIN sys_user u1 ON lm.sender_id = u1.user_id\n" +
            "\tJOIN sys_user u2 ON lm.recipient_id = u2.user_id \n" +
            "ORDER BY\n" +
            "\tlm.create_time DESC;")
    public List<MsgItemVO> selectMsgItem(@Param("userId") Long userId);
}




